package au.com.somethingwritten.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class BookDAO {
	private static Connection connection = null;
	private static Statement statement = null;
	private static ResultSet resultSet = null;
	
	public static ArrayList<Book> getAllBooks() throws Exception{
		ArrayList<Book> allResults = new ArrayList<Book>();
		try{
			//load the MySQL JDBC driver
			Class.forName("com.mysql.jdbc.Driver");
			//create the database connection
			connection = DriverManager
			.getConnection("jdbc:mysql://localhost/somethingwritten?"
			+ "user=laac&password=password");
			
			//create object for sending sql statements
			statement = connection.createStatement();
			//execute statement - select all books and get their authors.
			resultSet = statement.executeQuery("SELECT books.*, authors.fname, authors.lname " +
					"FROM books " +
					"LEFT JOIN authors " +
					"ON books.authorId = authors.id");
			
			//add results to List of Books
		    while (resultSet.next()) {
		    	//Get book details
		    	int id = resultSet.getInt("books.id");
		    	String isbn = resultSet.getString("isbn");
		    	String title = resultSet.getString("title").replaceAll("&", "and");
		    	
		    	String category = resultSet.getString("category");
		    	double price = resultSet.getDouble("price");
		    	int year = resultSet.getInt("year");
		    	String edition = resultSet.getString("edition");
		    	double discount = resultSet.getDouble("discount");
		    	int numCopies = resultSet.getInt("numCopies");
		    	String imgPath = resultSet.getString("imagePath");
		    	String authorFname = resultSet.getString("fname");
		    	String authorLname = resultSet.getString("lname");
		    	String description = resultSet.getString("description");
		    	
		    	//Create new book object
		    	Book tmp = new Book(id, isbn, title, (authorFname + " " + authorLname), 
		    			category, price, year, edition, discount, numCopies, imgPath, description);
		    	
		    	
		    	//Add book to results list
		    	allResults.add(tmp);
		    }
			      
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		
		return allResults;
	}
	
	public static int updateBookQuantity(Book book) throws Exception{
		int numRowsEffected = 0;
		
		try{
			//load the MySQL JDBC driver
			Class.forName("com.mysql.jdbc.Driver");
			//create the database connection
			connection = DriverManager
			.getConnection("jdbc:mysql://localhost/somethingwritten?"
			+ "user=laac&password=password");
			
			//create object for sending sql statements
			statement = connection.createStatement();
			//execute statement - update book
			numRowsEffected = statement.executeUpdate("UPDATE books " +
					"SET numCopies = " + book.getQuantity().toString() + " " +
					"WHERE id = " + book.getId().toString());
			      
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		
		return numRowsEffected;
	}
	
	
	
/***
 * Close all connect objects.
 */
private static void close() {
	try {
		if (resultSet != null) {
			resultSet.close();
		}
		if (statement != null) {
			statement.close();
		}
		if (connection != null) {
			connection.close();
		}
	} catch (Exception e) {
		
	}
}

}
